The following is from Azure Developer Training lab for AZ-203

Introduction to Table storage in Azure

Azure Table storage is a service that stores structured NoSQL data in the cloud, providing a key/attribute store with a schema less design. Because Table storage is schema less, it’s easy to adapt your data as the needs of your application evolve. Access to Table storage data is fast and cost-effective form any types of applications, and is typically lower in cost than traditional SQL for similar volumes of data.

You can use Table storage to store flexible datasets like user data for web applications, address books, device information, or other types of metadata your service requires. You can store any number of entities in a table, and as storage account may contain any number of tables, up to the capacity limit of the storage account.

Note: The content in this lesson applies to the original Azure Table storage.However, there is now a premium offering for table storage, the AzureCosmos DB Table API that offers throughput-optimized tables, global distribution, and automatic secondary indexes.

What is Table storage

Azure Table storage stores large amounts of structured data. The service is aNoSQL datastore which accepts authenticated calls from inside and outside the Azure cloud. Azure tables are ideal for storing structured, non-relational data. Common uses of Table storage include:

• Storing TBs of structured data capable of serving web scale applications
• Storing datasets that don’t require complex joins, foreign keys, or stored procedures and can be denormalized for fast access
• Quickly querying data using a clustered index
• Accessing data using the OData protocol and LINQ queries with WCFData Service .NET Libraries

You can use Table storage to store and query huge sets of structured, non-relational data, and your tables will scale as demand increases.

Table storage concepts

Table storage contains the following components:

• URL format: Azure Table Storage accounts use this format:http://<storageaccount>.table.core.windows.net/<table>
  • Azure Cosmos DB Table API accounts use this format:http://<storageaccount>.table.cosmosdb.azure.com/<table>
  • You can address Azure tables directly using this address with theOData protocol. For more information, see OData.org.
• Accounts: All access to Azure Storage is done through a storage account.
  • All access to Azure Cosmos DB is done through a Table API account.
• Table: A table is a collection of entities. Tables don’t enforce a schema on entities, which means a single table can contain entities that have different sets of properties.
• Entity: An entity is a set of properties, similar to a database row. An entity in Azure Storage can be up to 1MB in size. An entity in AzureCosmos DB can be up to 2MB in size.
• Properties: A property is a name-value pair. Each entity can include up to 252 properties to store data. Each entity also has three system properties that specify a partition key, a row key, and a timestamp.Entities with the same partition key can be queried more quickly, and inserted/updated in atomic operations. An entity’s row key is its unique identifier within a partition.

Choosing Table storage or Cosmos DB Table API

Azure Cosmos DB Table API and Azure Table storage share the same table data model and expose the same create, delete, update, and query operations through their SDKs.

If you currently use Azure Table Storage, you gain the following benefits by moving to the Azure Cosmos DB Table API:

Developing with Azure Table storage

Azure Table storage has these SDKs available for development:

• WindowsAzure.Storage .NET SDK. This library enables you to work with the storage Table service.
• Python SDK. The Azure Cosmos DB Table SDK for Python also supports the storage Table service.
• Azure Storage SDK for Java. This Azure Storage SDK provides a client library in Java to consume Azure Table storage.
• Node.js SDK. This SDK provides a Node.js package and a browser-compatible JavaScript client library to consume the storage Tableservice.
• AzureRmStorageTable PowerShell module. This PowerShell module has cmdlets to work with storage Tables.
• Azure Storage Client Library for C++. This library enables you to build applications against Azure Storage.
• Azure Storage Table Client Library for Ruby. This project provides aRuby package that makes it easy to access Azure storage Table services.
• Azure Storage Table PHP Client Library. This project provides a PHP client library that makes it easy to access Azure storage Table services.

Developing with the Azure Cosmos DB Table API

At this time, the Azure Cosmos DB Table API has four SDKs available fordevelopment:

• Microsoft.Azure.CosmosDB.Table .NET SDK. This library has the same classes and method signatures as the public Windows Azure StorageSDK, but also has the ability to connect to Azure Cosmos DB accounts using the Table API. Note that theMicrosoft.Azure.CosmosDB.Table library is currently available for .NET Standard only, it’s not yet available for .NET Core.
• Python SDK. The new Azure Cosmos DB Python SDK is the only SDK that supports Azure Table storage in Python. This SDK connects withboth Azure Table storage and Azure Cosmos DB Table API.
• Java SDK. This Azure Storage SDK has the ability to connect to AzureCosmos DB accounts using the Table API.
• Node.js SDK. This Azure Storage SDK has the ability to connect to AzureCosmos DB accounts using the Table API.

Guidelines for table design

Designing tables for use with the Azure storage table service is very different from design considerations for a relational database. This lesson describes guidelines for designing your Table service solution to be read efficient and write efficient.

Design your Table service solution to be read-efficient

• Design for querying in read-heavy applications. When you are designing your tables, think about the queries (especially the latency sensitive ones) that you will execute before you think about how you will update your entities. This typically results in an efficient and performant solution.
• Specify both PartitionKey and RowKey in your queries. Point queries such as these are the most efficient table service queries. Consider storing duplicate copies of entities. Table storage is cheap so consider storing the same entity multiple times (with different keys) to enable more efficient queries.
• Consider denormalizing your data. Table storage is cheap so consider denormalizing your data. For example, store summary entities so that queries for aggregate data only need to access a single entity.
• Use compound key values. The only keys you have are PartitionKey andRowKey. For example, use compound key values to enable alternate keyed access paths to entities.
• Use query projection. You can reduce the amount of data that you transfer over the network by using queries that select just the fields you need.

Design your Table service solution to be write-efficient

• Do not create hot partitions. Choose keys that enable you to spread your requests across multiple partitions at any point of time.
• Avoid spikes in traffic. Smooth the traffic over a reasonable period of time and avoid spikes in traffic.
• Don’t necessarily create a separate table for each type of entity. When you require atomic transactions across entity types, you can store these multiple entity types in the same partition in the same table.
• Consider the maximum throughput you must achieve. You must be aware of the scalability targets for the Table service and ensure that your design will not cause you to exceed them.

Design scalable and performant tables

To design scalable and performant tables, you must consider factors such as performance, scalability, and cost. If you have previously designed schemas for relational databases, these considerations are familiar, but while there are some similarities between the Azure Table service storage model and relational models, there are also important differences. These differences typically lead to different designs that may look counter-intuitive or wrong to someone familiar with relational databases, yet make sense if you are designing for a NoSQL key/value store such as the Azure Table service. Many of your design differences reflect the fact that the Table service is designed to support cloud-scale applications that can contain billions of entities (or rows in relational database terminology) of data or for datasets that must support high transaction volumes. Therefore, you must think differently about how you store your data and understand how the Table service works. A well-designedNoSQL data store can enable your solution to scale much further and at a lower cost than a solution that uses a relational database. This guide helps you with these topics.

About the Azure Table service

This section highlights some of the key features of the Table service that are especially relevant to designing for performance and scalability. Although the focus of this lesson is on the Table service, it includes discussion of the Azure Queue and Blob services, and how you might use them with the Table service.

What is the Table service? As you might expect from the name, the Table service uses a tabular format to store data. In the standard terminology, each row of the table represents an entity, and the columns store the various properties of that entity. Every entity has a pair of keys to uniquely identify it, and a timestamp column that the Table service uses to track when the entity was last updated. The timestamp is applied automatically, and you cannot manually overwrite the timestamp with an arbitrary value. The Table service uses this last-modified timestamp (LMT) to manage optimistic concurrency.

Note: The Table service REST API operations also return an ETag value that it derives from the LMT. This document uses the terms ETag and LMT interchangeably because they refer to the same underlying data.

The following example shows a simple table design to store employee and department entities.

So far, this data appears similar to a table in a relational database with the key differences being the mandatory columns, and the ability to store multiple entity types in the same table. Also, each of the user-defined properties such as FirstName or Age has a data type, such as integer or string, just like a column in a relational database. Although unlike in a relational database, the schema-less nature of the Table service means that a property need not have the same data type on each entity. To store complex data types in a single property, you must use a serialized format such as JSON or XML.

Your choice of PartitionKey and RowKey is fundamental to good table design. Every entity stored in a table must have a unique combination of PartitionKey and RowKey. As with keys in a relational database table, the PartitionKey and RowKey values are indexed to create a clustered index to enable fast look-ups. However, the Table service does not create any secondary indexes, so PartitionKey and RowKey are the only indexed properties.

A table comprises one or more partitions, and many of the design decisions you make will be around choosing a suitable PartitionKey and RowKey to optimize your solution. A solution may consist of a single table that contains all your entities organized into partitions, but typically a solution has multiple tables. Tables help you to logically organize your entities, help you manage access to the data using access control lists, and you can drop an entire table using a single storage operation.

Table partitions

The account name, table name, and PartitionKey together identify the partition within the storage service where the table service stores the entity.As well as being part of the addressing scheme for entities, partitions define a scope for transactions, and form the basis of how the table service scales.

In the Table service, an individual node services one or more complete partitions, and the service scales by dynamically load-balancing partitions across nodes. If a node is under load, the table service can split the range of partitions serviced by that node onto different nodes; when traffic subsides, the service can merge the partition ranges from quiet nodes back onto a single node.

Entity Group Transactions

In the Table service, Entity Group Transactions (EGTs) are the only built-in mechanism for performing atomic updates across multiple entities. EGTs are sometimes also referred to as batch transactions. EGTs can only operate on entities stored in the same partition (that is, share the same partition key in a given table). So anytime you require atomic transactional behavior across multiple entities, you must ensure that those entities are in the same partition.This is often a reason for keeping multiple entity types in the same table (and partition) and not using multiple tables for different entity types. A singleEGT can operate on at most 100 entities. If you submit multiple concurrentEGTs for processing, it is important to ensure those EGTs do not operate on  entities that are common across EGTs; otherwise, processing can be delayed.

EGTs also introduce a potential trade-off for you to evaluate in your design.That is, using more partitions increases the scalability of your application, because Azure has more opportunities for load balancing requests across nodes. But using more partitions might limit the ability of your application to perform atomic transactions and maintain strong consistency for your data.Furthermore, there are specific scalability targets at the level of a partition that might limit the throughput of transactions you can expect for a single node.

Capacity considerations

The following table describes some of the key values to be aware of when youare designing a Table service solution:

Cost considerations

Table storage is relatively inexpensive, but you should include cost estimates for both capacity usage and the quantity of transactions as part of your evaluation of any Table service solution. However, in many scenarios, storing denormalized or duplicate data in order to improve the performance or scalability of your solution is a valid approach.

Design for querying

Table service solutions may be read intensive, write intensive, or a mix of the two. This article focuses on the things to bear in mind when you are designing your Table service to support read operations efficiently. Typically, a design that supports read operations efficiently is also efficient for write operations.However, there are additional considerations to bear in mind when designing to support write operations.

A good starting point for designing your Table service solution to enable you to read data efficiently is to ask “What queries will my application need to execute to retrieve the data it needs from the Table service?”

Note: With the Table service, it’s important to get the design correct up front because it’s difficult and expensive to change it later. For example, in a relational database it’s often possible to address performance issues simply by adding indexes to an existing database: this is not an option with the Table service.

How your choice of PartitionKey and RowKey impacts query performance

The following examples assume the table service is storing employee entities with the following structure (most of the examples omit the Timestamp property for clarity):

These result in the following general guidelines for designing Table service queries. Note that the filter syntax used in the examples below is from theTable service REST API.

• A Point Query is the most efficient lookup to use and is recommended to be used for high-volume lookups or lookups requiring lowest latency.Such a query can use the indexes to locate an individual entity very efficiently by specifying both the PartitionKey and RowKey values. For example: $filter=(PartitionKey eq ‘Sales’) and (RowKey eq ‘2’)
• Second best is a Range Query that uses the PartitionKey and filters on a range of RowKey values to return more than one entity. ThePartitionKey value identifies a specific partition, and the RowKey values identify a subset of the entities in that partition. For example:$filter=PartitionKey eq ‘Sales’ and RowKey ge’S’ and RowKey lt ‘T’
• Third best is a Partition Scan that uses the PartitionKey and filters on another non-key property and that may return more than one entity. ThePartitionKey value identifies a specific partition, and the property valuess elect for a subset of the entities in that partition. For example:$filter=PartitionKey eq ‘Sales’ and LastName eq’Smith’
• A Table Scan does not include the PartitionKey and is very inefficient because it searches all of the partitions that make up your table in turn for any matching entities. It will perform a table scan regardless of whether or not your filter uses the RowKey. For example:$filter=LastName eq ‘Jones’
• Queries that return multiple entities return them sorted in PartitionKey and RowKey order. To avoid resorting the entities in the client, choose aRowKey that defines the most common sort order.

Note that using an “or” to specify a filter based on RowKey values results ina partition scan and is not treated as a range query. Therefore, you should avoid queries that use filters such as: $filter=PartitionKey eq’Sales’ and (RowKey eq ‘121’ or RowKey eq ‘322’)

Choosing an appropriate PartitionKey

Your choice of PartitionKey should balance the need to enable the use ofEGTs (to ensure consistency) against the requirement to distribute your entities across multiple partitions (to ensure a scalable solution).

At one extreme, you could store all your entities in a single partition, but this may limit the scalability of your solution and would prevent the table service from being able to load-balance requests. At the other extreme, you could store one entity per partition, which would be highly scalable and which enables the table service to load-balance requests, but which would prevent you from using entity group transactions.

An ideal PartitionKey is one that enables you to use efficient queries and that has sufficient partitions to ensure your solution is scalable. Typically, you will find that your entities will have a suitable property that distributes your entities across sufficient partitions.

Note: For example, in a system that stores information about users or employees, UserID may be a good PartitionKey. You may have several entities that use a given UserID as the partition key. Each entity that stores data about a user is grouped into a single partition, and so these entities are accessible via entity group transactions, while still being highly scalable.

Optimizing queries for the Table service

The Table service automatically indexes your entities using the PartitionKey and RowKey values in a single clustered index, hence the reason that point queries are the most efficient to use. However, there are no indexes other than that on the clustered index on the PartitionKey and RowKey.

Many designs must meet requirements to enable lookup of entities based on multiple criteria. For example, locating employee entities based on email, employee id, or last name.

Sorting data in the Table service

The Table service returns entities sorted in ascending order based onPartitionKey and then by RowKey. These keys are string values and to ensure that numeric values sort correctly, you should convert them to a fixed length and pad them with zeroes. For example, if the employee id value you use as the RowKey is an integer value, you should convert employee id 123 to00000123.

Authorization in Table Service

Authorize with Shared Key

Every request made against a storage service must be authorized, unless the request is for a blob or container resource that has been made available for public or signed access. One option for authorizing a request is by usingShared Key.

Use the Shared Key authorization scheme to make requests against the Table service using the REST API. Shared Key authorization for the Table service inversion 2009-09-19 and later uses the same signature string as in previous versions of the Table service.

An authorized request requires two headers: the Date or x-ms-date header and the Authorization header. The following sections describe how to construct these headers.

Specifying the Date Header

All authorized requests must include the Coordinated Universal Time (UTC)timestamp for the request. You can specify the timestamp either in the x-ms-date header, or in the standard HTTP/HTTPS Date header. If both headers are specified on the request, the value of x-ms-date is used as the request’s time of creation.

The storage services ensure that a request is no older than 15 minutes by the time it reaches the service. This guards against certain security attacks, including replay attacks. When this check fails, the server returns response code 403 (Forbidden).

Note: The x-ms-date header is provided because some HTTP client libraries and proxies automatically set the Date header, and do not give the developer an opportunity to read its value in order to include it in the authorized request. If you set x-ms-date, construct the signature with an empty value for the Date header.

Specifying the Authorization Header

An authorized request must include the Authorization header. If this header is not included, the request is anonymous and may only succeed against a container or blob that is marked for public access, or against a container, blob, queue, or table for which a shared access signature has been provided for delegated access.

To authorize a request, you must sign the request with the key for the account that is making the request and pass that signature as part of the request.

The format for the Authorization header is as follows:

Where SharedKey or SharedKeyLite is the name of the authorization scheme, AccountName is the name of the account requesting the resource, and Signature is a Hash-based Message Authentication Code (HMAC)constructed from the request and computed by using the SHA256 algorithm, and then encoded by using Base64 encoding.

Note: It is possible to request a resource that resides beneath a different account, if that resource is publicly accessible.

The following sections describe how to construct the Authorization header.

Constructing the Signature String

How you construct the signature string depends on which service and version you are authorizing against and which authorization scheme you are using.When constructing the signature string, keep in mind the following:

• The VERB portion of the string is the HTTP verb, such as GET or PUT, and must be uppercase.
• For Shared Key authorization for the Blob, Queue, and File services, each header included in the signature string may appear only once. If any header is duplicated, the service returns status code 400 (BadRequest).
• The values of all standard HTTP headers must be included in the string in the order shown in the signature format, without the header names.These headers may be empty if they are not being specified as part of the request; in that case, only the new-line character is required.
• If the x-ms-date header is specified, you may ignore the Dateheader, regardless of whether it is specified on the request, and simply specify an empty line for the Date portion of the signature string. It is acceptable to specify both x-ms-date and Date; in this case, the service uses the value of x-ms-date.
• If the x-ms-date header is not specified, specify the Date header in the signature string, without including the header name.
• All new-line characters (\n) shown are required within the signature string.
• The signature string includes canonicalized headers and canonicalized resource strings. Canonicalizing these strings puts them into a standard format that is recognized by Azure Storage. For detailed information on constructing the CanonicalizedHeaders andCanonicalizedResource strings that make up part of the signature string, see the appropriate sections later in this topic.

Table Service (Shared Key authorization)

The format of the signature string for Shared Key against the Table service is the same for all versions.

The Shared Key signature string for a request against the Table service differs slightly from that for a request against the Blob or Queue service, in that it does not include the CanonicalizedHeaders portion of the string.Additionally, the Date header in this case is never empty even if the request sets the x-ms-date header. If the request sets x-ms-date, that value is also used for the value of the Date header.

To encode the signature string for a request against the Table service made using the REST API, use the following format:

StringToSign = VERB + "\n" + Content-MD5 + "\n" + Content-Type + "\n" + Date + "\n" + CanonicalizedResource;

Note: Beginning with version 2009-09-19, the Table service requires that allREST calls include the DataServiceVersion andMaxDataServiceVersion headers.

Establishing a stored access policy

A stored access policy provides an additional level of control over service-level shared access signatures (SAS) on the server side. Establishing a stored access policy serves to group shared access signatures and to provide additional restrictions for signatures that are bound by the policy. You can use a stored access policy to change the start time, expiry time, or permissions fora signature, or to revoke it after it has been issued.

The following storage resources support stored access policies:

• Blob containers
• File shares
• Queues
• Tables

Note: Note that a stored access policy on a container can be associated with a shared access signature granting permissions to the container itself or to the blobs it contains. Similarly, a stored access policy on a file share can be associated with a shared access signature granting permissions to the share itself or to the files it contains.

Stored access policies are currently not supported for account SAS.

Creating or Modifying a Stored Access Policy

To create or modify a stored access policy, call the Set ACL operation for the resource with a request body that specifies the terms of the access policy. The body of the request includes a unique signed identifier of your choosing, up to64 characters in length, and the optional parameters of the access policy, as follows:

Table entity range restrictions (startpk, startrk, endpk, andendrk) cannot be specified in a stored access policy.

A maximum of five access policies may be set on a container, table, or queue at any given time. Each SignedIdentifier field, with its unique Idfield, corresponds to one access policy. Attempting to set more than five access policies at one time results in the service returning status code 400 (Bad Request).

Modifying or Revoking a Stored Access Policy

To modify the parameters of the stored access policy, you can call the access control list operation for the resource type to replace the existing policy, specifying a new start time, expiry time, or set of permissions. For example, if your existing policy grants read and write permissions to a resource, you can modify it to grant only read permissions for all future requests. In this case, the signed identifier of the new policy, as specified by the ID field, would be identical to the signed identifier of the policy you are replacing.

To revoke a stored access policy, you can either delete it, or rename it by changing the signed identifier. Changing the signed identifier breaks the associations between any existing signatures and the stored access policy.Deleting or renaming the stored access policy immediately effects all of the shared access signatures associated with it.

To remove a single access policy, call the resource’s Set ACL operation, passing in the set of signed identifiers that you wish to maintain on the container. To remove all access policies from the resource, call the Set ACL operation with an empty request body.

Cross-Origin Resource Sharing (CORS) Support for the Azure Storage Services

Beginning with version 2013-08-15, the Azure storage services supportCross-Origin Resource Sharing (CORS) for the Blob, Table, and Queue services. The File service supports CORS beginning with version 2015-02-21.

CORS is an HTTP feature that enables a web application running under one domain to access resources in another domain. Web browsers implement a security restriction known as same-origin policy that prevents a web page from calling APIs in a different domain; CORS provides a secure way to allow one domain (the origin domain) to call APIs in another domain. See theCORS specification for details on CORS.

You can set CORS rules individually for each of the storage services, by calling Set Blob Service Properties, Set File ServiceProperties, Set Queue Service Properties, and SetTable Service Properties. Once you set the CORS rules for the service, then a properly authenticated request made against the service from a different domain will be evaluated to determine whether it is allowed according to the rules you have specified.

Important: CORS is not an authentication mechanism. Any request made against a storage resource when CORS is enabled must either have a proper authentication signature, or must be made against a public resource. CORS is not supported for Premium Storage accounts.

Table service REST API

Table services resources

The Table service exposes the following resources via the REST API:

• Account. The storage account is a uniquely identified entity within the storage system. The storage account is the parent namespace for the Table service. All tables are associated with an account.
• Tables. The Tables resource represents the set of tables within a given storage account.
• Entity. An individual table stores data as a collection of entities.

Resource URI Syntax

The base URI for Table service resources is the storage account:

To list the tables in a given storage account, to create a new table, or to delete a table, refer to the set of tables in the specified storage account:

To return a single table, name that table within the Tables collection, as follows:

To query entities in a table, or to insert, update, or delete an entity, refer to that table directly within the storage account. This basic syntax refers to the set of all entities in the named table:

The format for addressing data resources for queries conforms to that specified by the OData Protocol Specification. You can use this syntax to filter entities based on criteria specified on the URI.

Note that all values for query parameters must be URL encoded before they are sent to the Azure storage services.

Supported HTTP Operations

Each resource supports operations based on the HTTP verbs GET, PUT, HEAD, and DELETE. The verb, syntax, and supported HTTP version(s) for each operation appears on the reference page for each operation. For a complete list of operation reference pages, see Table Service REST API.

Query timeout and pagination

The Table service supports the following two types of query operations:

• The Query Tables operation returns the list of tables within the specified storage account. The list of tables may be filtered according to criteria specified on the request.
• The Query Entities operation returns a set of entities from the specified table. Query results may be filtered according to criteria specified on the request.A query against the Table service may return a maximum of 1,000 items at one time and may execute for a maximum of five seconds. If the result set contains more than 1,000 items, if the query did not complete within five seconds, or if the query crosses the partition boundary, the response includes headers which provide the developer with continuation tokens to use in order to resume the query at the next item in the result set. Continuation token headers may be returned for a Query Tables operation or a Query Entities operation.Note that the total time allotted to the request for scheduling and processing the query is 30 seconds, including the five seconds for query execution.It is possible for a query to return no results but to still return a continuation header.The continuation token headers are shown in the following table.

To retrieve the continuation tokens and execute a subsequent query to return the next page of results, first inspect the response headers for continuation tokens. If there are no headers or the header values are null, there are no additional entities to return.

Note: When making subsequent requests that include continuation tokens, be sure to pass the original URI on the request. For example, if you have specified a $filter, $select, or $top query option as part of the original request, you will want to include that option on subsequent requests. Otherwise your subsequent requests may return unexpected results. The $top query option in this case specifies the maximum number of results per page, not the maximum number of results in the whole response set.

If you are handling continuation tokens manually using the Microsoft .NET Client Library, first cast the result of the query operation to a QueryOperationResponse object. You can then access the continuation token headers in the Headers property of the QueryOperationResponse object.

After you have retrieved the continuation tokens, use their values to construct a query to return the next page of results. A query request URI may take these parameters, which correspond to the continuation token headers returned with the response:

• NextTableName
• NextPartitionKey
• NextRowKey

The total time allotted to the request for scheduling and processing a query is 30 seconds, including five seconds for query execution.

If the operation is an insert, update, or delete operation, the operation may have succeeded on the server despite an error being returned by the client. This may happen when the client timeout is set to less than 30 seconds, which is the maximum timeout for aninsert, update, or delete operation.

Sample Response Headers and Subsequent Request

The following code example shows a set of sample response headers from an entity query against a table named Customers that returns continuation headers. Both x-ms-continuation-NextPartitionKey and x-ms-continuation-NextRowKeyare returned.

Date: Mon, 27 Jun 2016 20:11:08 GMT Content-Type: application/json;charset=utf-8 Server: Windows-Azure-Table/1.0 Microsoft-HTTPAPI/2.0 Cache-Control: no-cache x-ms-request-id: f9b2cd09-4dec-4570-b06d-4fa30179a58e x-ms-version: 2015-12-11 x-ms-continuation-NextPartitionKey: 1!8!U21pdGg- x-ms-continuation-NextRowKey: 1!8!QmVuOTk5 Content-Length: 880298

The request for the next page of data can be constructed like the following URI:

Querying Tables and Entities

Querying tables and entities in the Table service requires careful construction of the request URI. The following sections describe query options and demonstrate some common scenarios.

Basic Query Syntax

To return all of the tables in a given storage account, perform a GET operation on the Tables resource, as described in the Query Tables operation.

• The basic URI for addressing the Tables: https://myaccount.table.core.windows.net/Tables
• To return a single named table: https://myaccount.table.core.windows.net/Tables(‘MyTable’)
• To return all entities in a table, specify the table name on the URI without the Tables resource: https://myaccount.table.core.windows.net/MyTable()

Query results are sorted by PartitionKey, then by RowKey. Ordering results in any other way is not currently supported.

You can specify additional options to limit the set of tables or entities returned, as described below.

Supported Query Options

The Table service supports the following query options, which conform to the OData Protocol Specification. You can use these options to limit the set of tables, entities, or entity properties returned by a query.

Note: When making subsequent requests that include continuation tokens, be sure to pass the original URI on the request. For example, if you have specified a $filter, $select, or $top query option as part of the original request, you will want to include that option on subsequent requests. Otherwise your subsequent requests may return unexpected results. The $top query option in the case where results are paginated specifies the maximum number of results per page, not the maximum number of results in the whole response set.

Supported Comparison Operators

Within a $filter clause, you can use comparison operators to specify the criteria against which to filter the query results.

For all property types, the following comparison operators are supported:

Additionally, the following operators are supported for Boolean properties:

Query String Encoding

The following characters must be encoded if they are to be used in a query string:

• Forward slash (/)
• Question mark (?)
• Colon (:)
• ‘At’ symbol (@)
• Ampersand (&)
• Equals sign (=)
• Plus sign (+)
• Comma (,)
• Dollar sign ($)

Single quotes in query strings must be represented as two consecutive single quotes (”). For example, “o’clock” would be: o”clock.\

Sample Query Expressions

The following samples show how to construct the request URI for some typical entity queries using REST syntax.

Note that both the $top and $filter options can be used to filter on table names as well, using the syntax demonstrated for filtering on properties of type String.

Returning the Top n Entities

To return the top n entities for any query, specify the $top query option. The following example returns the top 10 entities from a table named Customers:

Filtering on the PartitionKey and RowKey Properties

Because the PartitionKey and RowKey properties form an entity’s primary key, you can use a special syntax to identify the entity, as follows:

Alternatively, you can specify these properties as part of the $filter option, as shown in the following section.

Note that the key property names and constant values are case-sensitive. Both the PartitionKey and RowKey properties are of type String.

Constructing Filter Strings

When constructing a filter string, keep these rules in mind:

• Use the logical operators defined by the OData Protocol Specification to compare a property to a value. Note that it is not possible to compare a property to a dynamic value; one side of the expression must be a constant.
• The property name, operator, and constant value must be separated by URL-encoded spaces. A space is URL-encoded as %20.
• All parts of the filter string are case-sensitive.
• The constant value must be of the same data type as the property in order for the filter to return valid results.

Note: Be sure to check whether a property has been explicitly typed before assuming it is of a type other than string. If a property has been explicitly typed, the type is indicated within the response when the entity is returned. If the property has not been explicitlytyped, it will be of type String, and the type will not be indicated within the response when the entity is returned.

Filtering on String Properties

When filtering on string properties, enclose the string constant in single quotes.

The following example filters on the PartitionKey and RowKey properties; additional non-key properties could also be added to the query string.

The following example filters on a FirstName and LastName property:

Note that the Table service does not support wildcard queries. However, you can perform prefix matching by using comparison operators on the desired prefix. The following example returns entities with a LastName property beginning with the letter ‘A’:

Filtering on Numeric Properties

To filter on an integer or floating-point number, specify the constant value on the URI without quotes.

This example returns all entities with an Age property whose value is greater than 30:

Filtering on Boolean Properties

To filter on a Boolean value, specify true or false without quotes. The following example returns all entities where the IsActive property is set to true:

Filtering on DateTime Properties

To filter on a DateTime value, specify the datetime keyword on the URI, followed by the date/time constant in single quotes. The date/time constant must be in combined UTC format.

The following example returns entities where the CustomerSince property is equal to July 10, 2008:

Filtering on GUID Properties

To filter on a GUID value, specify the guid keyword on the URI, followed by the guid constant in single quotes.

The following example returns entities where the GuidValue property is equal to :

Inserting and updating entities

To insert or update an entity, you include with the request an an OData ATOM or OData JSON entity that specifies the properties and data for the entity.

The Insert Entity operation inserts a new entity with a unique primary key, formed from the combination of the PartitionKey and the RowKey. The Update Entity operation replaces an existing entity with the same PartitionKey and RowKey.The Merge Entity operation updates the properties of an existing entity, but does not replace the entity. The Insert Or Merge Entity operation creates a new entity with a unique primary key or updates the properties of an existing entity, but does notreplace the entity. The Insert Or Replace Entity operation creates a new entity with a unique primary key or replaces an existing entity.

Constructing the Atom Feed

The Atom feed for an insert or update operation defines the entity’s properties by specifying their names and data types, and sets the values for those properties.

The content element contains the entity’s property definitions, which are specified within the m:properties element. The property’s type is specified by the m:type attribute.

Here is an example of an Atom feed for an Insert Entity operation:

<?xml version="1.0" encoding="utf-8" standalone="yes"?> <entry xmlns:d="http://schemas.microsoft.com/ado/2007/08/dataservices" xmlns:m="http://schemas.microsoft.com/ado/2007/08/dataservices/metadata" xmlns="http://www.w3.org/2005/Atom"> <title /> <author> <name /> </author> <id /> <content type="application/xml"> <m:properties> <d:Address>Mountain View</d:Address> <d:Age m:type="Edm.Int32">23</d:Age> <d:AmountDue m:type="Edm.Double">200.23</d:AmountDue> <d:BinaryData m:type="Edm.Binary" m:null="true" /> <d:CustomerCode m:type="Edm.Guid">c9da6455-213d-42c9-9a79-3e9149a57833</d:CustomerCode> <d:CustomerSince m:type="Edm.DateTime">2008-07-10T00:00:00</d:CustomerSince> <d:IsActive m:type="Edm.Boolean">true</d:IsActive> <d:NumOfOrders m:type="Edm.Int64">255</d:NumOfOrders> <d:PartitionKey>mypartitionkey</d:PartitionKey> <d:RowKey>myrowkey1</d:RowKey> </m:properties> </content> </entry>

Note: Atom payloads are supported only in versions prior to 2015-12-11. Beginning with version 2015-12-11, payloads must be in JSON.

Constructing the JSON Feed

To insert or update an entity using the OData JSON format, create a JSON object with property names as keys together with their property values. You may need to include the property type if it cannot be inferred through OData JSON type detection heuristics.

The JSON payload corresponding to the ATOM example above is as follows:

{ "Address":"Mountain View", "Age":23, "AmountDue":200.23, "CustomerCode@odata.type":"Edm.Guid", "CustomerCode":"c9da6455-213d-42c9-9a79-3e9149a57833", "CustomerSince@odata.type":"Edm.DateTime", "CustomerSince":"2008-07-10T00:00:00", "IsActive":true, "NumOfOrders@odata.type":"Edm.Int64", "NumOfOrders":"255", "PartitionKey":"mypartitionkey", "RowKey":"myrowkey" }

References